Snapshot isolation is a new isolation level that reduces blocking reads and allows transactions to read previously committed versions of data, instead of uncommitted data.
SQL Server's default isolation level of READ COMMITTED restricts transactions to reading only data that has been committed. This isolation level has the effect of blocking a transaction's read operation when the data is exclusively locked by another transaction. A lower isolation level called READ UNCOMITTED allows your transactions to read another transaction's uncommitted data while it is locked, but the values are not stable because the data is uncommitted. SQL Server 2005 introduces a new isolation level that provides a middle way between READ COMMITTED and READ UNCOMMITTED. The SNAPSHOT isolation level allows your transaction to read the previously committed version of the data that is currently being changed, so that your transaction's view of the data is consistent with the state of the data when your transaction began without being blocked by the other transaction. Snapshot isolation has the benefit of reducing blocking and deadlocking in your transactions that is due to blocking reads, without having to let your transactions read uncommitted data. (Note: this article is based on a pre-Beta 2 version of SQL Server called IDW3, build 9.00.790. Some details about the operation and behavior of snapshot isolation may change before the final version of SQL Server 2005 ships.)
Getting Started: SQL Server Isolation Levels in a Nutshell
Before SQL Server 2005, all transactions operated in the context of four isolation levels. These isolation levels affect the way that transactions take and hold shared locks on data resources. Shared locks are the locks taken when reading data; exclusive locks are the locks taken when changing data through an INSERT, UPDATE, or DELETE statement.
Higher transaction isolation levels come at a cost: the higher levels take more locks, and therefore increase the likelihood that your transaction may block others, and that your transaction will have to wait while other transactions release their locks. Isolation increases but concurrency decreases because transactions become more incompatible with each other as more shared locks are added into the mix.
In SQL Server, exclusive locks are never compatible with other exclusive locks. If your transaction updates a data row, no other transaction can update that row as long as your transaction is still running and has not been committed or rolled back. But shared locks are another story.
You can use the SQL Server isolation levels to prevent certain types of anomalies from occurring during your transaction due to reads or shared locks. These anomalies are called dirty read, non-repeatable read, and phantom read, respectively.
Dirty Reads
A dirty read occurs when a transaction reads data that is being changed by another transaction that has not yet been committed or rolled back; the data read cannot really be trusted. For example, let's suppose you have a transaction in which you SELECT data from a table and specify of range of values?let's say last names between the letters K through M. If an uncommitted transaction is changing a name in that range and your SELECT statement were to read the changed data, your transaction would be performing a dirty read.
The lowest isolation level, READ UNCOMMITTED, permits dirty reads. If your transaction is in the READ UNCOMMITTED isolation level, then your SELECT statement does not take any shared locks on the data in order to read it, and so it is not blocked by any transaction that has an exclusive lock on the data because it is changing it.
The next higher isolation level, READ COMMITTED, prevents dirty reads. If your transaction is in the READ COMMITTED isolation level, which is the SQL Server 2000 and SQL Server 2005 default, your SELECT statements will attempt to take shared locks on the data to be read, and your statement will be blocked by another transaction changing the data. Your transaction will be temporarily blocked because shared locks are not compatible with exclusive locks. After the other transaction is finished and either commits or rolls back, your SELECT statement will execute and you will only read the committed data.
However, in the READ COMMITTED isolation level, you are not guaranteed to see the same data at any time in the transaction. If at the beginning of the transaction, your SELECT statement of the range of values between K and M resulted in 1,000 rows, you could issue the same SELECT statement later and perhaps see fewer rows if some were deleted, more rows if some others were inserted, and the data itself could be changed. In the READ COMMITTED isolation level, SQL Server releases its shared locks on reads after finishing the SELECT statement, so other transactions may change the data.
Non-Repeatable Reads
A non-repeatable read occurs if you read data at one point in a transaction, and then later re-read the data, and the data is changed. The next higher isolation level, REPEATABLE READ, prevents non-repeatable reads. In the REPEATABLE READ isolation level, your transaction is guaranteed that any data you read will not change until your transaction is finished. If your SELECT statement at the beginning of your transaction resulted in 1,000 rows in the range between K and M, then at any time later in your transaction, if you re-issued the same SELECT statement, you will still see all 1,000 rows unchanged. The data you initially read will remain unchanged, eliminating non-repeatable reads.
SQL Server accomplishes the REPEATABLE READ isolation level by holding all shared locks that your transaction acquires until the end of your transaction. Every transaction, regardless of isolation level, already holds all exclusive locks until you end the transaction with a COMMIT or ROLLBACK; the REPEATABLE READ isolation level just adds your shared locks to the mix, keeping them until you end the transaction.
Phantoms
The REPEATABLE READ isolation level does not prevent phantoms: new rows that may be inserted into a range. For example, in a REPEATABLE READ you could issue your SELECT statement for values in the range of K through M and get 1,000 rows, and then later get 1,001 rows as a new row is inserted. Your transaction is still not completely isolated.
The strongest type of isolation level, SERIALIZABLE, ensures that all data that your transaction reads in a certain range of values will remain unchanged until the end of your transaction. Suppose your transaction is in the SERIALIZABLE isolation level. At the beginning of the transaction you issue a SELECT statement on the range of values of K through M and get 1,000 rows. The SERIALIZABLE isolation level guarantees that at any other time during your transaction, the same SELECT statement will result in exactly the same set of 1,000 rows, and they will be unchanged.
SQL Server 2000 and 2005 accomplish the SERIALIZABLE isolation level by using key-range locks, which are special shared locks that prevent inserts of new rows into a range of values. In the SERIALIZABLE isolation level, your transaction will behave as though it was the only transaction on the system.
Degrees of Isolation
Essentially, the various isolation levels in SQL Server allow you to adjust the degree of isolation for any given transaction. Remember, changing the isolation levels in a transaction just affects the degree to which the data you read in SELECT statements is protected from changes by other transactions. Any actual data changes that you make are guaranteed, at any isolation level, to be unaffected by other transactions until you finish.
If you enable snapshot isolation on a database that has a high volume of transactions, you can expect that tempdb will require considerable storage to keep up with the required versions of the data in addition to its usual tasks. That means that your operations personnel will need to closely monitor tempdb for snapshot activity.
At the lowest isolation level, READ UNCOMMITTED, your read activity is completely open to any changes by other transactions, whether they have committed or not, and your SELECT statements can read dirty data. At the highest level, SERIALIZABLE, your transaction will never see any SELECTed data change due to other transactions.
Higher transaction isolation levels come at a cost: the higher levels take more locks, and therefore increase the likelihood that your transaction may block others, and that your transaction will have to wait while other transactions release their locks. Isolation increases but concurrency decreases, because transactions become more incompatible with each other as more shared locks are added into the mix. That's the reason the default isolation level for SQL Server 2000 and 2005 is READ COMMITTED; it's high enough to make sure your transaction never sees dirty reads, but also low enough that your transaction will release its shared locks as soon as each SELECT statement is finished. So READ COMMITTED allows more concurrency than REPEATABLE READ and SERIALIZABLE, but ensures more consistency than READ UNCOMMITTED.
Overcoming Blocking in READ COMMITTED: Enter SNAPSHOT Isolation
Even in the READ COMMITTED isolation level, if another transaction is changing some data and therefore has exclusive locks on it, your transaction will have to wait until those exclusive locks are released before it can gain its brief shared locks and read the data. SQL Server can block the read activity of a SELECT statement in the READ COMMITTED isolation level.
But suppose your SELECT statement could just read an older version of the data, one that was consistent with when your transaction began, so that your transaction didn't have to wait, but also wouldn't be reading dirty data? That is what SNAPSHOT isolation will accomplish.
Snapshot Isolation Basics
SQL Server 2005 introduces a new type of isolation level that is termed "non-blocking READ COMMITTED" in Books Online. In other words, SNAPSHOT isolation behaves like READ COMMITTED in that your transactions will not ever see dirty reads, but on the other hand, they do not take shared locks that could block other transactions.
Enabling Snapshot Isolation
You must first enable snapshot isolation at the database level. By default, SQL Server 2005 databases will not be enabled for snapshot isolation, so you must issue an ALTER DATABASE command if you desire it. For example, the following code will enable snapshot isolation in the AdventureWorks sample database:
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
With SQL Server 2005 Beta 2, the Master and MSDB databases are enabled for snapshot isolation by default. In addition, the sample databases AdventureWorks and AdventureWorksDW have also been enabled for snapshot isolation.
You can detect whether a database has been enabled for snapshot isolation in SQL Server 2005 by issuing a SELECT command against the new sys.databases system view:
SELECT name
, snapshot_isolation_framework
FROM sys.databases
(There is also a new 'SnapshotIsolationFramework' property in the DATABASEPROPERTYEX() function documented, but it does not work in SQL Server 2005 IDW3, and may not be in later builds.)
Levels of Snapshot Isolation
Once you have enabled a database for snapshot isolation, you have the option of establishing it at one of two possible levels. The first is as a special case within the default READ COMMITTED isolation level. It was originally called 'statement-level' snapshot isolation in Beta 1, and has been renamed READ_COMMITTED_SNAPSHOT for Beta 2. The second is the transaction-oriented SNAPSHOT isolation level.
With READ_COMMITTED_SNAPSHOT (known as statement-level isolation in Beta 1), you establish snapshot isolation for all your transactions that occur within the default READ COMMITTED isolation level. When enabled, you do not need to specify the snapshot isolation level before starting a transaction. For Beta 2, you can set this using ALTER DATABASE, whereas with Yukon Beta 1, you have to use the trace flag 3970 when starting up SQL Server. Because READ_COMMITTED_SNAPSHOT, as of IDW3, is still a work in progress and not fully documented, in this article I'll focus exclusively on the snapshot isolation level.
Transaction-level snapshot isolation requires you to explicitly change the isolation level to SNAPSHOT for each transaction that you want to execute at the snapshot isolation level. The following code snippet shows how you can set the isolation level in a transaction to SNAPSHOT:
USE AdventureWorks
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
SELECT EmployeeID
, BaseRate
FROM HumanResources.Employee
WHERE Title='Network Manager'
The advantage of statement-level snapshot isolation is that all transactions automatically support it, and you don't have to specifically set it before you begin a transaction. The disadvantage is that you have to set it using a trace flag. The real difference between the two levels is whether SNAPSHOT or READ COMMITTED is the default isolation level. In this article, we will assume that SQL Server's default isolation level is READ COMMITTTED, so whenever you see example transactions using the snapshot isolation level, you'll also see the explicit SET command just before the transaction begins.
How Snapshot Isolation Works
Just as soon as you enable snapshot isolation for a database, SQL Server 2005 stores copies of all database data changes in the tempdb database. This allows SQL Server 2005 to version the updates that occur in a database.
When a change occurs to a row, it is marked with a timestamp of the change and linked to a copy of the previously committed row in tempdb. Successive prior versions are stored in tempdb using a linked list, whereas the newest committed value is always stored in a page in the database where the change originated.
When a user in a snapshot isolation transaction attempts to read some data that has been changed, SQL Server 2005 retrieves the version of the row consistent with the time the users' transaction started. If the row has been changed since the transaction started, SQL Server will then traverse the linked list chain of row pointers in tempdb and retrieve the correct version of the data row.
Consider the code snippet of the prior section. Any change to the HumanResources.Employee row that occurs after your transaction has begun will cause the SELECT statement to read the older committed version of the then-current row.
The timestamps and links for versioned rows are maintained in an additional 14 bytes that are added to each changed row in the database. The copied row stored in tempdb also contains an additional 14 bytes for timestamp and linking to other row versions. If you set snapshot isolation off in the database, the space in tempdb is freed up, and as soon as the committed rows in the database that were versioned are updated again, SQL Server 2005 will free up the row's 14 byte snapshot overhead.
Snapshot Isolation and Blocking
Snapshot isolation can help reduce blocking by removing the shared locks that a transaction's SELECT statement normally takes, but without having to read uncommitted data. Table 1 illustrates how two transactions running side by side will behave when the reading transaction is using the snapshot isolation level. (This example is based on an example in SQL Server 2005 Books Online, "Understanding Snapshot Isolation.")
Listing 1 contains the code steps for User A, and Listing 2 contains the code steps for User B. You can step along with the following analysis by loading each listing into its own SQL Server 2005 Management Studio (formerly known as SQL Workbench) window, and executing step by step.
To start, notice that at time T1 both users begin a transaction, though only User A sets the isolation level to SNAPSHOT. At time T2, User A SELECTs a value from the HumanResources.Employee table, and sees the current committed value of 39.6635. The key step now occurs at time T3 when User B changes the value to 40.0:
UPDATE HumanResources.Employee
SET BaseRate = 40.0
WHERE EmployeeID = 150
At time T4, User A re-issues the original SELECT, and still sees the currently committed value, not the new but as yet uncommitted value of 40.0.
There are a couple of important observations to make here. First, in the default READ COMMITTED isolation level, User A's second SELECT statement at time T4 would have been blocked. That's because the SELECT statement would have attempted a shared lock on the row being updated by User B, whereas User B has not yet released its exclusive lock on the row. Since shared locks are not compatible with exclusive locks, User A would simply have to wait until User B commits the transaction. However, since User A is running its transaction at the snapshot isolation level, no shared locks are requested and instead, the current committed version of the row is read from tempdb.
In the READ COMMITTED isolation level, if User A's SELECT statement at time T4 had used the NOLOCK hint, the statement would not have been blocked. But then User A would read dirty data. Similarly, if User A's SELECT statement had used the READPAST hint, the statement would not have been blocked, but the SELECT statement would not have read any data at all. Under READ COMMITTED, there is no way to read committed data without blocking; only the SNAPSHOT isolation level makes that possible.
Second, notice that the initial SELECT statement that User A makes at time T2 is not really necessary. A user transaction in the snapshot isolation level reads data that was committed as of the time the user's transaction began. The initial SELECT statement made no difference, as you can see for yourself if you vary the steps of the transactions accordingly.
The elegance of snapshot isolation consists in the fact that User A reads data that was correct, consistent, and committed at the time its transaction began. The underlying data did change during User A's transaction, but as long as the data remained uncommitted, it did not affect User A's transaction.
(Note that in the example, at time T6, after User B has committed its change to the row, User A still reads the older version of the data. This behavior may change in future builds. The original example in SQL Server 2005 Books Online suggests that User A should read the new value as soon as it is committed by User B. However, SQL Server 2005 IDW3 requires User A to commit the transaction before the newly committed value is read.)
Snapshot Isolation and Deadlocking
Once you've stepped through an example of how snapshot isolation can reduce blocking, it's easy to understand how snapshot isolation can reduce deadlocking. In a nutshell, deadlocking is just mutual blocking. When two transactions block each other, but neither can finish because each is requesting locks on the other's resources, neither can finish and SQL Server will detect the deadly embrace and cancel one of the queries. (For more information about deadlocking in SQL Server 2000, see "Resolving Deadlocks in SQL Server 2000," CoDe Magazine, Sep/Oct 2003.)
Table 2 shows two transactions operating side by side in a way that would cause a deadlock if they were both under the default READ COMMITTED isolation level. Both User A and User B finish their updates at times T2 and T3, respectively. In the default READ COMMITTED or higher isolation level, both transactions would be blocked when they attempt to read data that the other user is currently changing. The mutual blocking at time T5 would result in a deadlock, and SQL Server would choose a deadlock victim.
With snapshot isolation, though, each User's transaction now reads a prior committed version of the data, and no blocking occurs. Each transaction is free to continue. The upshot is that snapshot isolation can help reduce deadlocks that are caused by blocking reads.
Costs and Benefits of Snapshot Isolation
The major benefit of snapshot isolation is that you can now have consistent reads of data in a transaction that does not block other transactions and does not read uncommitted data. Instead, your transactions can read versions of data appropriate for when each transaction started.
Costs of Snapshot Isolation
The costs of snapshot isolation are significant and require some planning. Because snapshot isolation must store copies of all versioned data in the tempdb database, you can expect some processing overhead due to copying and maintaining versions and some I/O overhead due to storing and retrieving versions in tempdb.
Even more important is the amount of space that tempdb could use. If you enable snapshot isolation on a database that has a high volume of transactions, you can expect that tempdb will require considerable storage to keep up with the required versions of the data in addition to its usual tasks. That means that your operations personnel will need to closely monitor tempdb for snapshot activity.
Measuring Cost versus Benefit
When should you use snapshot isolation? Because its purpose is to eliminate blocking reads, the most natural use of snapshot isolation is in contexts where it is important that your transactions not read uncommitted data, but where reads of one transaction often tend to block or deadlock with other transactions. For this context, snapshot isolation is a real winner.
However, if your transactions hardly ever roll back, and/or reading uncommitted data does not cause problems in the application, the overhead of snapshot isolation may outweigh the benefit. In this type of context, you may find it better to just add NOLOCK or READPAST hints to sensitive queries to stop them from blocking.
In addition, there may be contexts where your transactions must be assured complete serializability, and in that context, snapshot isolation cannot replace the stronger REPEATABLE READ and SERIALIZABLE isolation levels.
Conclusion
Snapshot isolation is a new isolation level provided by SQL Server 2005. The snapshot isolation level is designed to help read operations no longer block, or be blocked by, write operations. Snapshot isolation fits in between the READ UNCOMMITTED and READ COMMITTED isolation levels. Like the READ UNCOMMITTED isolation level, SNAPSHOT does not take shared locks on data, and like READ COMMITTED, SNAPSHOT does not read uncommitted data.
Snapshot isolation can help reduce blocking and deadlocking due to blocking reads. If you have transactions that must read data that other transactions might be changing, and you do not want the transactions to read uncommitted data, then the new snapshot isolation level can fill that need. Because snapshot isolation stores versions of data in tempdb, however, it is important that you plan for increased temdb activity and increased space requirements if you want to use snapshot isolation.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT *
FROM authors WITH (HOLDLOCK)
